﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Register.IDAL;
using Register.DBUtility;
using System.Data.SqlClient;
using System.Data;
using Register.Model;

namespace Register.DAL
{
    /// <summary>
    /// FileName: DepartmentsDAL.cs
    /// CLRVersion: 4.0.30319.269
    /// Author: WangYan
    /// Corporation: 
    /// Description: 
    /// DateTime: 2012/6/27 11:33:01
    /// </summary>
  public  class DepartmentsDAL : IDepartmentsDAL
    {

      private String tableName = "System_departmentsInfo";

        public int AddDepartments(Model.DepartmentsInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into System_departmentsInfo(");
            strSql.Append("Departments_ID,Hospital_ID,Departments_Name,Departments_Introduce,Departments_AddTime,Departments_Recoder,Departments_LastAmendTime,Departments_LastAmendPerson,Departments_State)");
            strSql.Append(" values (");
            strSql.Append("@Departments_ID,@Hospital_ID,@Departments_Name,@Departments_Introduce,@Departments_AddTime,@Departments_Recoder,@Departments_LastAmendTime,@Departments_LastAmendPerson,@Departments_State)");
            SqlParameter[] parameters = {
					new SqlParameter("@Departments_ID", SqlDbType.Char,14),
					new SqlParameter("@Hospital_ID", SqlDbType.Char,5),
					new SqlParameter("@Departments_Name", SqlDbType.VarChar,20),
					new SqlParameter("@Departments_Introduce", SqlDbType.VarChar,300),
					new SqlParameter("@Departments_AddTime", SqlDbType.DateTime),
					new SqlParameter("@Departments_Recoder", SqlDbType.VarChar,20),
					new SqlParameter("@Departments_LastAmendTime", SqlDbType.DateTime),
					new SqlParameter("@Departments_LastAmendPerson", SqlDbType.VarChar,20),
					new SqlParameter("@Departments_State", SqlDbType.Char,1)};
            parameters[0].Value = model.Departments_ID;
            parameters[1].Value = model.Hospital_ID;
            parameters[2].Value = model.Departments_Name;
            parameters[3].Value = model.Departments_Introduce;
            parameters[4].Value = model.Departments_AddTime;
            parameters[5].Value = model.Departments_Recoder;
            parameters[6].Value = model.Departments_LastAmendTime;
            parameters[7].Value = model.Departments_LastAmendPerson;
            parameters[8].Value = model.Departments_State;

           return  DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        }

        public int UpdateDepartments(Model.DepartmentsInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update System_departmentsInfo set ");
            strSql.Append("Hospital_ID=@Hospital_ID,");
            strSql.Append("Departments_Name=@Departments_Name,");
            strSql.Append("Departments_Introduce=@Departments_Introduce,");
            strSql.Append("Departments_LastAmendTime=@Departments_LastAmendTime,");
            strSql.Append("Departments_LastAmendPerson=@Departments_LastAmendPerson ");
            strSql.Append(" where Departments_ID=@Departments_ID");
            SqlParameter[] parameters = {
					new SqlParameter("@Hospital_ID", SqlDbType.Char,5),
					new SqlParameter("@Departments_Name", SqlDbType.VarChar,20),
					new SqlParameter("@Departments_Introduce", SqlDbType.VarChar,300),
					new SqlParameter("@Departments_LastAmendTime", SqlDbType.DateTime),
					new SqlParameter("@Departments_LastAmendPerson", SqlDbType.VarChar,20),
					new SqlParameter("@Departments_ID", SqlDbType.Char,14)};
            parameters[0].Value = model.Hospital_ID;
            parameters[1].Value = model.Departments_Name;
            parameters[2].Value = model.Departments_Introduce;
            parameters[3].Value = model.Departments_LastAmendTime;
            parameters[4].Value = model.Departments_LastAmendPerson;
            parameters[5].Value = model.Departments_ID;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters); 
        }

        public int DeleteDepartments(Model.DepartmentsInfo Info)
        {
            String strSql = " update System_departmentsInfo set Departments_State='0' ";
            strSql = strSql + " where Departments_ID =@ID";
            SqlParameter[] parameters = new SqlParameter[] { 
            new SqlParameter("@ID", SqlDbType.Char,14)
            };
            parameters[0].Value = Info.Departments_ID;

            return DbHelperSQL.ExecuteSql(strSql,parameters);
        }

        public int GetDepartmentsCount(string where)
        {
            int count = 0;
            StringBuilder str = new StringBuilder();
            str.Append(" select count(*) from ");
            str.Append(tableName);
            if (where != null)
            {
                str.Append(" where ");
                str.Append(where);
                count = (int)DbHelperSQL.GetSingle(str.ToString());
            }
            else
            {
                count = (int)DbHelperSQL.GetSingle(str.ToString());
            }
            return count;
        }
        

        public List<Model.DepartmentsInfo> GetPagedDepartmentsInfo(int size, int pageIndex, string where)
        {
            return DataSetToList(DbHelperSQL.getPagedData(" * ", " Departments_AddTime ", where, tableName, OrderBy.DESC, pageIndex, size));
        }

        private List<Model.DepartmentsInfo> DataSetToList(DataSet dataSet)
        {
            List<DepartmentsInfo> modelList = new List<DepartmentsInfo>();

            DataTable dt = dataSet.Tables[0];
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                DepartmentsInfo model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = new DepartmentsInfo();
                    model.Departments_ID = dt.Rows[n]["Departments_ID"].ToString();
                    model.Hospital_ID = dt.Rows[n]["Hospital_ID"].ToString();
                    model.Departments_Name = dt.Rows[n]["Departments_Name"].ToString();
                    model.Departments_Introduce = dt.Rows[n]["Departments_Introduce"].ToString();
                    if (dt.Rows[n]["Departments_AddTime"].ToString() != "")
                    {
                        model.Departments_AddTime = DateTime.Parse(dt.Rows[n]["Departments_AddTime"].ToString());
                    }
                    model.Departments_Recoder = dt.Rows[n]["Departments_Recoder"].ToString();
                    if (dt.Rows[n]["Departments_LastAmendTime"].ToString() != "")
                    {
                        model.Departments_LastAmendTime = DateTime.Parse(dt.Rows[n]["Departments_LastAmendTime"].ToString());
                    }
                    model.Departments_LastAmendPerson = dt.Rows[n]["Departments_LastAmendPerson"].ToString();
                    model.Departments_State = dt.Rows[n]["Departments_State"].ToString();
                    modelList.Add(model);
                }
            }
            return modelList;
        }

        public Model.DepartmentsInfo GetAdminInfo(string DepartmentsID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 Departments_ID,Hospital_ID,Departments_Name,Departments_Introduce,Departments_AddTime,Departments_Recoder,Departments_LastAmendTime,Departments_LastAmendPerson,Departments_State from System_departmentsInfo ");
            strSql.Append(" where Departments_ID=@Departments_ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@Departments_ID", SqlDbType.Char,14)};
            parameters[0].Value = DepartmentsID;

            return DataSetToList(DbHelperSQL.Query(strSql.ToString(), parameters)).First();
        }
    }
}
